<?php

/**
 * Subclass for performing query and update operations on the 'post_tag' table.
 *
 * 
 *
 * @package lib.model
 */ 
class PostTagPeer extends BasePostTagPeer
{
  public static function retrieveByTag($tag)
  {
    $c = new Criteria();
    $c->add(TagPeer::HANDLE, $tag);
    $c->addJoin(PostTagPeer::TAG_ID, TagPeer::ID, Criteria::LEFT_JOIN);
    return PostTagPeer::doSelectJoinPost($c);
  }
  
  public static function getMaxPosts()
  {
    /*                    
    SELECT tmp.tag, MAX(count) as nb FROM (SELECT post_tag.tag_id AS tag, post_tag.post_id, COUNT( post_tag.tag_id ) AS count
    FROM post_tag
    LEFT JOIN post ON post_tag.post_id = post.id
    WHERE post.published = '1'
    GROUP BY post_tag.post_id) as tmp                    
    GROUP BY tmp.tag
    ORDER BY nb DESC
    LIMIT 0,1  
    */
    
    $con = sfContext::getInstance()->getDatabaseConnection('propel');
    
    // define the base query
    $query = "SELECT tmp.tag, MAX(count) as nb FROM ("
             ." SELECT ".PostTagPeer::TAG_ID." AS tag, COUNT( ".PostTagPeer::TAG_ID." ) AS count "
             ." FROM ".PostTagPeer::TABLE_NAME." LEFT JOIN ".PostPeer::TABLE_NAME
             ." ON ".PostTagPeer::POST_ID." = ".PostPeer::ID
             ." WHERE ".PostPeer::PUBLISHED." = '1' "
             ." GROUP BY ".PostTagPeer::POST_ID.") as tmp "                    
             ." GROUP BY tmp.tag "
             ." ORDER BY nb DESC ";  
    
    // prepare the statement
    $stmt = $con->prepareStatement($query);
    $stmt->setOffset(0);
    $stmt->setLimit(1);
    
    
    $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
    
    // Manage the results  
    while ($rs->next())
    {
      $max = $rs->getInt(1);
    }    
    return $max;               
  }  
}
